Database System Concepts, 5

Ed.

©Silberschatz, Korth and Sudarshan

See www.db-book.com for conditions on re-use

Chapter 2: Relational Model

©Silberschatz, Korth and Sudarshan2.2Database System Concepts - 5th Edition, Oct 5, 2006

Chapter 2: Relational Model



Structure of Relational Databases



Fundamental Relational-Algebra-Operations



Additional Relational-Algebra-Operations



Extended Relational-Algebra-Operations



Null Values



Modification of the Database

©Silberschatz, Korth and Sudarshan2.3Database System Concepts - 5th Edition, Oct 5, 2006

Example of a Relation

©Silberschatz, Korth and Sudarshan2.4Database System Concepts - 5th Edition, Oct 5, 2006

Basic Structure



Formally, given sets D

, D

, …. D

a relation ris a subset of

x D

x … x D

Thus, a relation is a set of n-tuples (a

, …, a

) where each a

∈D



Example: If

customer_name = {Jones, Smith, Curry, Lindsay, …}

/* Set of all customer names */

customer_street = {Main, North, Park, …} /* set of all street names*/

customer_city = {Harrison, Rye, Pittsfield, …} /* set of all city names */

Then r= { (Jones, Main, Harrison),

(Smith, North, Rye),

(Curry, North, Rye),

(Lindsay, Park, Pittsfield) }

is a relation over

customer_name x customer_street x customer_city

©Silberschatz, Korth and Sudarshan2.5Database System Concepts - 5th Edition, Oct 5, 2006

Attribute Types



Each attribute of a relation has a name



The set of allowed values for each attribute is called the domain of the

attribute



Attribute values are (normally) required to be atomic; that is, indivisible

E.g. the value of an attribute can be an account number,

but cannot be a set of account numbers



Domain is said to be atomic if all its members are atomic



The special value null is a member of every domain



The null value causes complications in the definition of many operations

We shall ignore the effect of null values in our main presentation

and consider their effect later

©Silberschatz, Korth and Sudarshan2.6Database System Concepts - 5th Edition, Oct 5, 2006

Relation Schema



, A

, …, A

are attributes



R= (A

, A

, …, A

) is a relation schema

Example:

Customer_schema = (customer_name, customer_street, customer_city)



r(R) denotes a relation r on the relation schema R

Example:

customer (Customer_schema)

©Silberschatz, Korth and Sudarshan2.7Database System Concepts - 5th Edition, Oct 5, 2006

Relation Instance



The current values (relation instance) of a relation are specified by

a table



An element tof ris a tuple, represented by a row in a table

Jones

Smith

Curry

Lindsay

customer_name

Main

North

Park

customer_street

Harrison

Rye

Pittsfield

customer_city

customer

attributes

(or columns)

tuples

(or rows)

©Silberschatz, Korth and Sudarshan2.8Database System Concepts - 5th Edition, Oct 5, 2006

Relations are Unordered



Order of tuples is irrelevant (tuples may be stored in an arbitrary order)



Example: account relation with unordered tuples

©Silberschatz, Korth and Sudarshan2.9Database System Concepts - 5th Edition, Oct 5, 2006

Database



A database consists of multiple relations



Information about an enterprise is broken up into parts, with each relation

storing one part of the information

account : stores information about accounts

depositor : stores information about which customer

owns which account

customer : stores information about customers



Storing all information as a single relation such as

bank(account_number, balance, customer_name, ..)

results in

repetition of information

e.g.,if two customers own an account (What gets repeated?)

the need for null values

e.g., to represent a customer without an account



Normalization theory (Chapter 7) deals with how to design relational schemas

©Silberschatz, Korth and Sudarshan2.10Database System Concepts - 5th Edition, Oct 5, 2006

The

The customer

customer Relation

Relation

©Silberschatz, Korth and Sudarshan2.11Database System Concepts - 5th Edition, Oct 5, 2006

The

The depositor

depositor Relation

Relation

©Silberschatz, Korth and Sudarshan2.12Database System Concepts - 5th Edition, Oct 5, 2006

Keys



Let K ⊆R



K is a superkey of Rif values for Kare sufficient to identify a unique tuple of

each possible relation r(R)

by “possible r ” we mean a relation rthat could exist in the enterprise we

are modeling.

Example: {customer_name, customer_street} and

{customer_name}

are both superkeys of Customer, if no two customers can possibly have

the same name

In real life, an attribute such as customer_id would be used instead of

customer_name to uniquely identify customers, but we omit it to keep

our examples small, and instead assume customer names are unique.

©Silberschatz, Korth and Sudarshan2.13Database System Concepts - 5th Edition, Oct 5, 2006

Keys (Cont.)



Kis a candidate key if Kis minimal

Example: {customer_name} is a candidate key for Customer, since it

is a superkey and no subset of it is a superkey.



Primary key: a candidate key chosen as the principal means of

identifying tuples within a relation

Should choose an attribute whose value never, or very rarely,

changes.

E.g. email address is unique, but may change

©Silberschatz, Korth and Sudarshan2.14Database System Concepts - 5th Edition, Oct 5, 2006

Foreign Keys



A relation schema may have an attribute that corresponds to the primary

key of another relation. The attribute is called a foreign key.

E.g. customer_name and account_number attributes of depositor are

foreign keys to customer and account respectively.

Only values occurring in the primary key attribute of the referenced

relation may occur in the foreign key attribute of the referencing

relation.



Schema diagram

©Silberschatz, Korth and Sudarshan2.15Database System Concepts - 5th Edition, Oct 5, 2006

Query Languages



Language in which user requests information from the database.



Categories of languages

Procedural

Non-procedural, or declarative



“Pure” languages:

Relational algebra

Tuple relational calculus

Domain relational calculus



Pure languages form underlying basis of query languages that people

use.

©Silberschatz, Korth and Sudarshan2.16Database System Concepts - 5th Edition, Oct 5, 2006

Relational Algebra



Procedural language



Six basic operators

select:

project: ∏

union: ∪

set difference: –

Cartesian product: x

rename:



The operators take one or two relations as inputs and produce a new

relation as a result.

©Silberschatz, Korth and Sudarshan2.17Database System Concepts - 5th Edition, Oct 5, 2006

Select Operation

Select Operation –

–Example

Example



Relation r A B C D



A=B ^ D > 5

(r)

A B C D

©Silberschatz, Korth and Sudarshan2.18Database System Concepts - 5th Edition, Oct 5, 2006

Select Operation



Notation:

(r)



pis called the selection predicate



Defined as:

(r) = {t| t∈rand p(t)}

Where pis a formula in propositional calculus consisting of terms

connected by : ∧(and), ∨(or), ¬(not)

Each term is one of:

where op is one of: =, ≠, >, ≥. <. ≤



Example of selection:

branch_name=“Perryridge”

(account)

©Silberschatz, Korth and Sudarshan2.19Database System Concepts - 5th Edition, Oct 5, 2006

Project Operation

Project Operation –

–Example

Example



Relation r:A B C

A C

∏

A,C

(r)

©Silberschatz, Korth and Sudarshan2.20Database System Concepts - 5th Edition, Oct 5, 2006

Project Operation



Notation:

where A

, A

are attribute names and ris a relation name.



The result is defined as the relation of kcolumns obtained by erasing

the columns that are not listed



Duplicate rows removed from result, since relations are sets



Example: To eliminate the branch_name attribute of account

∏

account_number, balance

(account)

)(

,,,

21 r

AAA K

∏

©Silberschatz, Korth and Sudarshan2.21Database System Concepts - 5th Edition, Oct 5, 2006

Union Operation

Union Operation –

–Example

Example



Relations r, s:



r ∪s:

A B

©Silberschatz, Korth and Sudarshan2.22Database System Concepts - 5th Edition, Oct 5, 2006

Union Operation



Notation: r∪s



Defined as:

r∪s= {t| t∈ror t∈s}



For r∪sto be valid.

1. r, s must have the same arity (same number of attributes)

2. The attribute domains must be compatible (example: 2

column

of rdeals with the same type of values as does the 2

column of s)



Example: to find all customers with either an account or a loan

∏

customer_name

(depositor) ∪ ∏

customer_name

(borrower)

©Silberschatz, Korth and Sudarshan2.23Database System Concepts - 5th Edition, Oct 5, 2006

Set Difference Operation

Set Difference Operation –

–Example

Example



Relations r, s:



r – s:

A B

©Silberschatz, Korth and Sudarshan2.24Database System Concepts - 5th Edition, Oct 5, 2006

Set Difference Operation



Notation r – s



Defined as:

r – s = {t| t∈rand t ∉s}



Set differences must be taken between compatible

relations.

rand smust have the same arity

attribute domains of r and s must be compatible

©Silberschatz, Korth and Sudarshan2.25Database System Concepts - 5th Edition, Oct 5, 2006

Cartesian

Cartesian-

-Product Operation

Product Operation –

–Example

Example

Relations r, s:

rxs:

A B

ββββ

C D

ββγα

ββγ

C D

ββγ

©Silberschatz, Korth and Sudarshan2.26Database System Concepts - 5th Edition, Oct 5, 2006

Cartesian

Cartesian-

-Product Operation

Product Operation



Notation r xs



Defined as:

rx s= {t q |t ∈r and q ∈s}



Assume that attributes of r(R) and s(S) are disjoint. (That is, R∩S=

∅



If attributes of r(R) and s(S) are not disjoint, then renaming must be

used.

©Silberschatz, Korth and Sudarshan2.27Database System Concepts - 5th Edition, Oct 5, 2006

Composition of Operations



Can build expressions using multiple operations



Example: σ

A=C

(r x s)



r x s



A=C

(r x s)

A B

ββββ

C D

ββ

γα

ββγ

A B C D E

ββ

©Silberschatz, Korth and Sudarshan2.28Database System Concepts - 5th Edition, Oct 5, 2006

Rename Operation



Allows us to name, and therefore to refer to, the results of relational-

algebra expressions.



Allows us to refer to a relation by more than one name.



Example:

(E)

returns the expression Eunder the name X



If a relational-algebra expression Ehas arity n, then

returns the result of expression Eunder the name X, and with the

attributes renamed to A1 , A

, …., A

)(

),...,,(

21 E

AAAx

©Silberschatz, Korth and Sudarshan2.29Database System Concepts - 5th Edition, Oct 5, 2006

Banking Example

branch (branch_name, branch_city, assets)

customer (customer_name, customer_street, customer_city)

account (account_number, branch_name, balance)

loan (loan_number, branch_name, amount)

depositor (customer_name, account_number)

borrower (customer_name, loan_number)

©Silberschatz, Korth and Sudarshan2.30Database System Concepts - 5th Edition, Oct 5, 2006

Example Queries



Find all loans of over $1200



Find the loan number for each loan of an amount greater than

$1200

amount > 1200

(loan)

∏

loan_number

(σ

amount > 1200

(loan))



Find the names of all customers who have a loan, an account, or both,

from the bank

∏

customer_name

(borrower) ∪ ∏

customer_name

(depositor)

©Silberschatz, Korth and Sudarshan2.31Database System Concepts - 5th Edition, Oct 5, 2006

Example Queries



Find the names of all customers who have a loan at the Perryridge

branch.



Find the names of all customers who have a loan at the

Perryridge branch but do not have an account at any branch of

the bank.

∏

customer_name

(σ

branch_name = “Perryridge”

(σ

borrower.loan_number = loan.loan_number

(borrower x loan))) –

∏

customer_name

(depositor)

∏

customer_name

(

branch_name=“Perryridge”

(

borrower.loan_number = loan.loan_number

(borrower x

loan)))

©Silberschatz, Korth and Sudarshan2.32Database System Concepts - 5th Edition, Oct 5, 2006

Example Queries



Find the names of all customers who have a loan at the Perryridge branch.

Query 2

∏

customer_name

(σ

loan.loan_number = borrower.loan_number

(

(σ

branch_name = “Perryridge”

(loan)) x borrower))

Query 1

∏

customer_name

(σ

branch_name = “Perryridge”

(

borrower.loan_number = loan.loan_number

(borrower x loan)))

©Silberschatz, Korth and Sudarshan2.33Database System Concepts - 5th Edition, Oct 5, 2006

Example Queries

©Silberschatz, Korth and Sudarshan2.34Database System Concepts - 5th Edition, Oct 5, 2006

Example Queries



Find the largest account balance

Strategy:

Find those balances that are not the largest

–Rename account relation as d so that we can compare each

account balance with all others

Use set difference to find those account balances that were not found

in the earlier step.

The query is:

∏

balance

(account) -∏

account.balance

(σ

account.balance < d.balance

(account x

(account)))

©Silberschatz, Korth and Sudarshan2.35Database System Concepts - 5th Edition, Oct 5, 2006

Formal Definition



A basic expression in the relational algebra consists of either one of the

following:

A relation in the database

A constant relation



Let E

and E

be relational-algebra expressions; the following are all

relational-algebra expressions:

E

∪E

E

–E

E

x E



), Pis a predicate on attributes in E

∏

), Sis a list consisting of some of the attributes in E



), x is the new name for the result of E

©Silberschatz, Korth and Sudarshan2.36Database System Concepts - 5th Edition, Oct 5, 2006

Additional Operations

We define additional operations that do not add any power to the

relational algebra, but that simplify common queries.



Set intersection



Natural join



Division



Assignment

©Silberschatz, Korth and Sudarshan2.37Database System Concepts - 5th Edition, Oct 5, 2006

Set

Set-

-Intersection Operation

Intersection Operation



Notation: r∩s



Defined as:



r∩s= { t | t∈rand t∈s}



Assume:

r, shave the same arity

attributes of rand sare compatible



Note: r∩s= r– (r–s)

©Silberschatz, Korth and Sudarshan2.38Database System Concepts - 5th Edition, Oct 5, 2006

Set

Set-

-Intersection Operation

Intersection Operation –

–Example

Example



Relation r, s:



r∩s

A B

β2

r s

A B

α2

©Silberschatz, Korth and Sudarshan2.39Database System Concepts - 5th Edition, Oct 5, 2006



Notation: r s

Natural

Natural-

-Join Operation

Join Operation



Let rand sbe relations on schemas Rand Srespectively.

Then, r s is a relation on schema R ∪Sobtained as follows:

Consider each pair of tuples t

from rand t

from s.

If t

and t

have the same value on each of the attributes in R∩S, add

a tuple tto the result, where

thas the same value as t

on r

thas the same value as t

on s



Example:

R= (A, B, C, D)

S= (E, B, D)

Result schema = (A, B, C, D, E)

r s is defined as:

∏

r.A, r.B, r.C, r.D, s.E

(

r.B = s.B

∧

r.D = s.D

(r x s))

©Silberschatz, Korth and Sudarshan2.40Database System Concepts - 5th Edition, Oct 5, 2006

Natural Join Operation

Natural Join Operation –

–Example

Example



Relations r, s:

A B

βγαδ

C D

αγβγβ

βγδ

∈

A B

αδ

C D

αγγβ

αγαγδ



r s

©Silberschatz, Korth and Sudarshan2.41Database System Concepts - 5th Edition, Oct 5, 2006

Division Operation

Notation:

Suited to queries that include the phrase “for all”.

Let rand sbe relations on schemas Rand Srespectively

where

R= (A

, …, A

, B

, …, B

)

S= (B

, …, B

)

The result of r ÷s is a relation on schema

R–S = (A

, …, A

)

r ÷s= { t| t ∈ ∏

R-S

(r) ∧ ∀ u ∈s ( tu ∈r ) }

Where tu means the concatenation of tuples tand uto

produce a single tuple

r ÷s

©Silberschatz, Korth and Sudarshan2.42Database System Concepts - 5th Edition, Oct 5, 2006

Division Operation

Division Operation –

–Example

Example

Relations r, s:

r÷s:A

A B

βγδδδ

∈

©Silberschatz, Korth and Sudarshan2.43Database System Concepts - 5th Edition, Oct 5, 2006

Another Division Example

A B

ββγγγ

C D

αγγγγγγβ

Relations r, s:

r÷s:

A B

αγ

γγ

©Silberschatz, Korth and Sudarshan2.44Database System Concepts - 5th Edition, Oct 5, 2006

Division Operation (Cont.)



Property

Let q = r ÷s

Then qis the largest relation satisfying qx s⊆r



Definition in terms of the basic algebra operation

Let r(R) and s(S) be relations, and let S ⊆R

r÷s= ∏R-S (r ) – ∏R-S ( ( ∏R-S (r ) x s ) – ∏R-S,S(r ))

To see why

∏R-S,S (r) simply reorders attributes of r

∏R-S (r) x s : all possible pairs between R-S (r ) and s

∏R-S (r) x s –∏R-S,S(r): those pairs that do not qualify, i.e., do not

appear in r(R).

∏R-S (∏R-S (r) x s ) – ∏R-S,S(r) ) gives those tuples t in

∏R-S (r ) such that for some tuple u ∈s, tu ∉

∉∉

∉r.

©Silberschatz, Korth and Sudarshan2.45Database System Concepts - 5th Edition, Oct 5, 2006

Assignment Operation



The assignment operation (←) provides a convenient way to express

complex queries.

Write query as a sequential program consisting of

a series of assignments

followed by an expression whose value is displayed as a result of

the query.

Assignment must always be made to a temporary relation variable.



Example: Write r÷sas

temp1 ← ∏

R-S

(r )

temp2 ← ∏

R-S

((temp1 x s ) – ∏

R-S,S

(r ))

result = temp1 –temp2

The result to the right of the ←is assigned to the relation variable on

the left of the ←.

May use variable in subsequent expressions.

©Silberschatz, Korth and Sudarshan2.46Database System Concepts - 5th Edition, Oct 5, 2006

Bank Example Queries



Find the names of all customers who have a loan and an account at

bank.

∏

customer_name

(borrower) ∩ ∏

customer_name

(depositor)



Find the name of all customers who have a loan at the bank and the

loan amount

∏

customer_name, loan_number, amount

(borrower loan)

©Silberschatz, Korth and Sudarshan2.47Database System Concepts - 5th Edition, Oct 5, 2006

Query 1

∏customer_name (

branch_name = “Downtown

”

(depositor account )) ∩

∏customer_name (

branch_name = “Uptown

”

(depositor account))

Query 2

∏customer_name, branch_name (depositor account)

temp(branch_name

)

({(“Downtown” ), (“Uptown” )})

Note that Query 2 uses a constant relation.

Bank Example Queries



Find all customers who have an account from at least the “Downtown”

and the Uptown” branches.

©Silberschatz, Korth and Sudarshan2.48Database System Concepts - 5th Edition, Oct 5, 2006



Find all customers who have an account at all branches located in

Brooklyn city.

Bank Example Queries

∏

customer_name, branch_name

(depositor account)

÷ ∏

branch_name

(σ

branch_city = “Brooklyn”

(branch))

©Silberschatz, Korth and Sudarshan2.49Database System Concepts - 5th Edition, Oct 5, 2006

Extended Relational

Extended Relational-

-Algebra

Algebra-

-Operations

Operations



Generalized Projection



Aggregate Functions



Outer Join

©Silberschatz, Korth and Sudarshan2.50Database System Concepts - 5th Edition, Oct 5, 2006

Generalized Projection



Extends the projection operation by allowing arithmetic functions to be

used in the projection list.



Eis any relational-algebra expression



Each of F

, F

, …, F

are are arithmetic expressions involving

constants and attributes in the schema of E.



Given relation credit_info(customer_name, limit, credit_balance), find

how much more each person can spend:

∏customer_name, limit – credit_balance (credit_info)

)

( ,...,,

FFF

∏

©Silberschatz, Korth and Sudarshan2.51Database System Concepts - 5th Edition, Oct 5, 2006

Aggregate Functions and Operations



Aggregation function takes a collection of values and returns a single

value as a result.

avg: average value

min: minimum value

max: maximum value

sum: sum of values

count: number of values



Aggregate operation in relational algebra

Eis any relational-algebra expression

G

, G

…, G

is a list of attributes on which to group (can be

empty)

Each F

is an aggregate function

Each A

is an attribute name

)(

)(,,(),(,,,

221121

nnn

AFAFAFGGG KK

©Silberschatz, Korth and Sudarshan2.52Database System Concepts - 5th Edition, Oct 5, 2006

Aggregate Operation

Aggregate Operation –

–Example

Example



Relation r:

A B



sum(c)

(r) sum(c )

©Silberschatz, Korth and Sudarshan2.53Database System Concepts - 5th Edition, Oct 5, 2006

Aggregate Operation

Aggregate Operation –

–Example

Example



Relation account grouped by branch-name:

branch_name

sum(balance)

(

account

)

branch_name account_number balance

Perryridge

Brighton

Redwood

A-102

A-201

A-217

A-215

A-222

400

900

750

700

branch_name sum(balance)

Perryridge

Brighton

Redwood

1300

1500

700

©Silberschatz, Korth and Sudarshan2.54Database System Concepts - 5th Edition, Oct 5, 2006

Aggregate Functions (Cont.)



Result of aggregation does not have a name

Can use rename operation to give it a name

For convenience, we permit renaming as part of aggregate

operation

branch_name

sum(balance) as sum_balance

(

account

)

©Silberschatz, Korth and Sudarshan2.55Database System Concepts - 5th Edition, Oct 5, 2006

Outer Join



An extension of the join operation that avoids loss of information.



Computes the join and then adds tuples form one relation that does

not match tuples in the other relation to the result of the join.



Uses null values:



null

signifies that the value is unknown or does not exist

All comparisons involving null are (roughly speaking) false by

definition.

We shall study precise meaning of comparisons with nulls later

©Silberschatz, Korth and Sudarshan2.56Database System Concepts - 5th Edition, Oct 5, 2006

Outer Join

Outer Join –

–Example

Example



Relation loan



Relation borrower

customer_name loan_number

Jones

Smith

Hayes

L-170

L-230

L-155

3000

4000

1700

loan_number amount

L-170

L-230

L-260

branch_name

Downtown

Redwood

Perryridge

Outer Join

Outer Join –

–Example

Example



Join

loan borrower

loan_number amount

L-170

L-230

3000

4000

customer_name

Jones

Smith

branch_name

Downtown

Redwood

Jones

Smith

null

loan_number amount

L-170

L-230

L-260

3000

4000

1700

customer_namebranch_name

Downtown

Redwood

Perryridge



Left Outer Join

loan borrower

Outer Join

Outer Join –

–Example

Example

loan_number amount

L-170

L-230

L-155

3000

4000

null

customer_name

Jones

Smith

Hayes

branch_name

Downtown

Redwood

null

loan_number amount

L-170

L-230

L-260

L-155

3000

4000

1700

null

customer_name

Jones

Smith

null

Hayes

branch_name

Downtown

Redwood

Perryridge

null



Full Outer Join

loan borrower



Right Outer Join

loan borrower

Null Values



It is possible for tuples to have a null value, denoted by null, for some

of their attributes



null signifies an unknown value or that a value does not exist.



The result of any arithmetic expression involving null is null.



Aggregate functions simply ignore null values (as in SQL)



For duplicate elimination and grouping, null is treated like any other

value, and two nulls are assumed to be the same (as in SQL)

Null Values



Comparisons with null values return the special truth value:

unknown

If false was used instead of unknown, then not (A < 5)

would not be equivalent to A >= 5



Three-valued logic using the truth value unknown:

OR: (unknown or true) = true,

(unknown or false) = unknown

(unknown or unknown)= unknown

AND: (true and unknown)= unknown,

(false and unknown)= false,

(unknown and unknown)= unknown

NOT: (not unknown)= unknown

In SQL “Pis unknown” evaluates to true if predicate Pevaluates

to unknown



Result of select predicate is treated as false if it evaluates to

unknown

Modification of the Database



The content of the database may be modified using the following

operations:

Deletion

Insertion

Updating



All these operations are expressed using the assignment

operator.

Deletion



A delete request is expressed similarly to a query, except

instead of displaying tuples to the user, the selected tuples are

removed from the database.



Can delete only whole tuples; cannot delete values on only

particular attributes



A deletion is expressed in relational algebra by:

r←r–E

where ris a relation and Eis a relational algebra query.

Deletion Examples



Delete all account records in the Perryridge branch.



Delete all accounts at branches located in Needham.

←

branch_city = “Needham”

(account branch )

← ∏

account_number,branch_name, balance

)

← ∏

customer_name, account_number

depositor)

account ←account – r

depositor ←depositor – r



Delete all loan records with amount in the range of 0 to 50

loan ←loan –

amount

≥

and amount

≤

(loan)

account ←account –

branch_name = “Perryridge”

(account )

Insertion



To insert data into a relation, we either:

specify a tuple to be inserted

write a query whose result is a set of tuples to be inserted



in relational algebra, an insertion is expressed by:

r ←r∪E

where ris a relation and Eis a relational algebra expression.



The insertion of a single tuple is expressed by letting Ebe a constant

relation containing one tuple.

Insertion Examples



Insert information in the database specifying that Smith has $1200 in

account A-973 at the Perryridge branch.



Provide as a gift for all loan customers in the Perryridge

branch, a $200 savings account. Let the loan number serve

as the account number for the new savings account.

account ←account ∪{(“A-973”, “Perryridge”, 1200)}

depositor ←depositor ∪{(“Smith”, “A-973”)}

←(σ

branch_name = “Perryridge”

(borrower loan))

account ←account ∪ ∏

loan_number,

branch_name,

200

)

depositor ←depositor ∪ ∏

customer_name, loan_number

)

Updating



A mechanism to change a value in a tuple without charging all values in

the tuple



Use the generalized projection operator to do this task



Each F

is either

the I

attribute of r, if the I

attribute is not updated, or,

if the attribute is to be updated F

is an expression, involving only

constants and the attributes of r, which gives the new value for the

attribute

)(

,,,,

FFF K

∏

←

Update Examples



Make interest payments by increasing all balances by 5 percent.



Pay all accounts with balances over $10,000 6 percent interest

and pay all others 5 percent

account ← ∏

account_number, branch_name, balance * 1.06

(σ

BAL

10000

(account ))

∪ ∏

account_number, branch_name, balance * 1.05

(σ

BAL

≤

10000

(account))

account ← ∏

account_number, branch_name, balance * 1.05

(account)

Database System Concepts, 5

Ed.

See www.db-book.com for conditions on re-use

End of Chapter 2

Figure 2.3. The

Figure 2.3. The branch

branch relation

relation

Figure 2.6: The

Figure 2.6: The loan

loan relation

relation

Figure 2.7: The

Figure 2.7: The borrower

borrower relation

relation

Figure 2.9

Result of

Result of σ

branch_name = “Perryridge”

(loan)

Figure 2.10:

Loan number and the amount of the loan

Figure 2.11: Names of all customers who

have either an account or an loan

Figure 2.12:

Customers with an account but no loan

Figure 2.13: Result of

Figure 2.13: Result of borrower

borrower |X|

|X| loan

loan

Figure 2.14

Figure 2.15

Figure 2.16

Figure 2.17

Largest account balance in the bank

Figure 2.18: Customers who live on the

same street and in the same city as

Smith

Figure 2.19: Customers with both an

account and a loan at the bank

Figure 2.20

Figure 2.21

Figure 2.22

Figure 2.23

Figure 2.24: The

Figure 2.24: The credit_info

credit_info relation

relation

Figure 2.25

Figure 2.26: The

Figure 2.26: The pt_works

pt_works relation

relation

Figure 2.27

The

The pt_works

pt_works relation after regrouping

relation after regrouping

Figure 2.28

Figure 2.29

Figure 2.30

The

The employee

employee and

and ft_works relations

ft_works relations

Figure 2.31

Figure 2.32

Figure 2.33

Figure 2.34